
if exists (select 1 from sysobjects where name = 'get_abteilungeninschema' and type = 'P')
begin
   drop procedure get_abteilungeninschema
   print 'Procedure: get_abteilungeninschema deleted ...'
end
go
create procedure get_abteilungeninschema(
  @schemaid          int           = 25
)
as
begin
  set nocount on   
   
  select abteilungid = a.AbteilungID,
         name = a.Name
    from Schemas s
   inner join PlanSchema ps
      on ps.SchemaID = s.SchemaID
   inner join Abteilung a
      on a.AbteilungID = ps.AbteilungID
   where s.SchemaID = @schemaid
     and ps.PrsID = 0

end
go
print 'Procedure: get_abteilungeninschema done ...'
go
grant exec on get_abteilungeninschema to prsadmins with grant option
go
grant exec on get_abteilungeninschema to prsusers
go

